package com.le.ui;

import com.le.util.Plot;
import com.le.util.Query;
import com.le.util.ScreenUtils;
import org.jfree.data.category.DefaultCategoryDataset;

import javax.swing.*;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Vector;

public class GradeManagePanel extends JPanel {
    JTable studentGradeTable;
    JButton addGrade = new JButton("成绩录入");
    JButton plotButton = new JButton("进行柱状图分析");
    JButton sortButton = new JButton("按总成绩排序");
    JButton savaFile = new JButton("导出Excel成绩表");
    FileDialog savaFileDialog = new FileDialog((Dialog) SwingUtilities.getWindowAncestor(this), "选择要保存的路径", FileDialog.SAVE);
    String hostIdAddress;
    String major;
    String classNumber;
    JLabel tipsLabel;
    JLabel avgTotalGradeLabel = new JLabel("总成绩均分:");
    JLabel soutAvgTotalGrade;

    public GradeManagePanel(String hostIdAddress, String major, String classNumber) {
        this.hostIdAddress = hostIdAddress;
        this.major = major;
        this.classNumber = classNumber;
    }

    public JPanel init() {
        tipsLabel = new JLabel(major + classNumber + "班");
        tipsLabel.setFont(new Font("隶书", Font.BOLD, 18));
        tipsLabel.setForeground(Color.red);
        //得到成绩表格数据
        Query query = new Query(hostIdAddress);
        query.setDatabaseName("sams");
        String sql;
        //求各科平均成绩
        sql = "select cname, avg(grade) from student, sc, course\n" +
                "where major='" + major + "' and class=" + classNumber + " and student.sno=sc.sno and sc.cno=course.cno\n" +
                "group by cname;";
        query.setSQL(sql);
        String[][] avgGrade = query.getRecord();
        sql = "select  student.sno, sname, gender, cname, grade from student, sc, course where major='" + major + "' and class="
                + classNumber + " and student.sno=sc.sno and sc.cno=course.cno";
        query.setSQL(sql);
        String[][] result = query.getRecord();
        for (int i = 0; i < result.length; i++) {
            result[i][0] = result[i][0].substring(10, 12);
        }
        Vector<Vector<String>> studentGradeData = new Vector<>();
        Vector<String> tableHead = new Vector<>(Arrays.asList("学号", "姓名", "性别", "数学", "数学均分", "Java", "Java均分", "体育", "体育均分", "英语", "英语均分", "总分"));
        for (int i = 0; i < result.length / 4; i++) {
            Vector<String> oneStudent = new Vector<>(Arrays.asList(result[4 * i]).subList(0, 3));
            int totalScore = 0;
            for (int j = 0; j < 4; j++) {
                totalScore = totalScore + Integer.parseInt(result[4 * i + j][4]);
                oneStudent.add(result[4 * i + j][4]);
                oneStudent.add(Integer.toString((int) Double.parseDouble(avgGrade[j][1])));
            }
            oneStudent.add(Integer.toString(totalScore));
            studentGradeData.add(oneStudent);
        }
        DefaultTableCellRenderer defaultTableCellRenderer = new DefaultTableCellRenderer();
        defaultTableCellRenderer.setHorizontalAlignment(SwingConstants.CENTER);
        studentGradeTable = new JTable(studentGradeData, tableHead);
        studentGradeTable.setEnabled(false);
        studentGradeTable.setDefaultRenderer(Object.class, defaultTableCellRenderer);
        this.setLayout(new BorderLayout());
        this.add(new JScrollPane(studentGradeTable));

        //计算总成绩均分
        TableModel studentTableModel = studentGradeTable.getModel();
        int rowCounts = studentTableModel.getRowCount();
        int columnCount = studentTableModel.getColumnCount();
        Long total = 0L;
        for (int i = 0; i < rowCounts; i++) {
            total = total + Long.parseLong((String) (studentTableModel.getValueAt(i, 11)));
        }
        soutAvgTotalGrade = new JLabel(Long.toString(total / rowCounts));
        soutAvgTotalGrade.setFont(new Font("隶书", Font.PLAIN, 18));
        soutAvgTotalGrade.setForeground(Color.blue);

        //按总成绩排序
        TableRowSorter<TableModel> sorter = new TableRowSorter<>(studentTableModel);
        java.util.List<RowSorter.SortKey> sortKeyList = new ArrayList<>();
        sortKeyList.add(new RowSorter.SortKey(11, SortOrder.DESCENDING));
        sorter.setSortKeys(sortKeyList);

        Box southPanel = Box.createHorizontalBox();
        Box northPanel = Box.createHorizontalBox();
        northPanel.add(Box.createHorizontalStrut(245));
        northPanel.add(tipsLabel);
        northPanel.add(Box.createHorizontalStrut(16));
        northPanel.add(avgTotalGradeLabel);
        northPanel.add(soutAvgTotalGrade);

        southPanel.add(addGrade);
        southPanel.add(Box.createHorizontalStrut(10));
        southPanel.add(sortButton);
        southPanel.add(Box.createHorizontalStrut(10));
        southPanel.add(savaFile);
        southPanel.add(Box.createHorizontalStrut(10));
        southPanel.add(plotButton);

        this.add(northPanel, BorderLayout.NORTH);
        this.add(southPanel, BorderLayout.SOUTH);

        //事件处理
        addGrade.addActionListener((ActionEvent e) -> {
            new GradeAddPage(hostIdAddress).init();
        });

        sortButton.addActionListener((ActionEvent e) -> {
            studentGradeTable.setRowSorter(sorter);
        });

        savaFile.addActionListener((ActionEvent e) -> {
            savaFileDialog.setVisible(true);
            //获取选择的路径及文件
            String directory = savaFileDialog.getDirectory();
            String file = savaFileDialog.getFile();
            System.out.println("打开的文件路径为" + directory);
            System.out.println("打开的文件名称为" + file);
            String path = directory + file;

            //从表模型中得到数据集
            String[][] dataSet = new String[rowCounts + 1][columnCount];
            dataSet[0] = new String[]{"学号", "姓名", "性别", "数学", "数学均分", "Java", "Java均分", "体育", "体育均分", "英语", "英语均分", "总分"};
            for (int i = 0; i < rowCounts; i++) {
                for (int j = 0; j < columnCount; j++) {
                    dataSet[i + 1][j] = (String) studentTableModel.getValueAt(i, j);
                }
            }
            Query.toExcel(dataSet, path);
        });

        plotButton.addActionListener((ActionEvent e) -> {
            String selectedSimpleSno = JOptionPane.showInputDialog("请输入要进行成绩柱状图分析的短学号");
            //获取相应学生所在行
            int selectedRow = Integer.parseInt(selectedSimpleSno) - 1;
            String selectedName = (String) studentTableModel.getValueAt(selectedRow, 1);
            //获取选中学生成绩
            Double math = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 3));
            Double java = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 5));
            Double gm = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 7));
            Double english = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 9));
            //获取平均成绩
            Double mathAvg = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 4));
            Double javaAvg = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 6));
            Double gmAvg = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 8));
            Double englishAvg = Double.parseDouble((String) studentTableModel.getValueAt(selectedRow, 10));
            JFrame plotJFrame = new JFrame(selectedName + "各科目成绩柱状图分析");
            DefaultCategoryDataset dataset = new DefaultCategoryDataset();
            dataset.addValue(math, selectedName, "数学");
            dataset.addValue(mathAvg, "班级均分", "数学");
            dataset.addValue(java, selectedName, "Java");
            dataset.addValue(javaAvg, "班级均分", "Java");
            dataset.addValue(gm, selectedName, "体育");
            dataset.addValue(gmAvg, "班级均分", "体育");
            dataset.addValue(english, selectedName, "英语");
            dataset.addValue(englishAvg, "班级均分", "英语");

            plotJFrame.setBounds((ScreenUtils.getScreenWidth() - 720) / 2,
                    (ScreenUtils.getScreenHeight() - 480) / 2, 720, 480);
            plotJFrame.add(Plot.plotBar(dataset, selectedName + "各科目成绩柱状图分析", "科目", "成绩"));
            plotJFrame.setPreferredSize(new Dimension(720, 480));
            plotJFrame.setResizable(false);
            plotJFrame.setIconImage(new ImageIcon("images//学生信息-彩色.png").getImage());
            plotJFrame.pack();
            plotJFrame.setDefaultCloseOperation(WindowConstants.HIDE_ON_CLOSE);
            plotJFrame.setVisible(true);
        });
        return this;
    }
}
